USE [dba]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_collationconvert]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_collationconvert]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_executeSql]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_executeSql]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_error_handler]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_error_handler]
GO

IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'tblSql' AND ss.name = N'dbo')
DROP TYPE [dbo].[tblSql]
GO


CREATE TYPE [dbo].[tblSql] AS TABLE
(
	[id] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED,
	[db] [nvarchar](max) NOT NULL,
	[sql] [nvarchar](max) NOT NULL
)
GO


CREATE PROCEDURE [dbo].[usp_error_handler]
AS 
DECLARE	@errmsg nvarchar(2048),
		@severity tinyint,
		@state tinyint,
		@errno int,
		@proc sysname,
		@lineno int
           
SELECT	@errmsg = ERROR_MESSAGE(),
		@severity = ERROR_SEVERITY(),
		@state = ERROR_STATE(),
		@errno = ERROR_NUMBER(),
		@proc = ERROR_PROCEDURE(),
		@lineno = ERROR_LINE()

IF @errmsg NOT LIKE '***%'
	SELECT	@errmsg = '*** ' + COALESCE(QUOTENAME(@proc), '<dynamic SQL>') + ', ' + LTRIM(STR(@lineno)) + '. Errno ' + LTRIM(STR(@errno)) + ': ' + @errmsg

RAISERROR(@errmsg, @severity, @state)
GO


CREATE PROCEDURE [dbo].[usp_executeSql] @tblSql tblSql READONLY, @txtOnly bit = 1
AS
SET ARITHABORT, NOCOUNT, NUMERIC_ROUNDABORT, XACT_ABORT ON;
DECLARE @tc int; SET @tc = @@TRANCOUNT;

DECLARE	@errorNumber int,
		@errorSeverity int,
		@errorState int,
		@errorProcedure nvarchar(max),
		@errorLine int,
		@errorMessage nvarchar(max);

DECLARE	@crlf nvarchar(2) = CHAR(13) + CHAR(10),
		@db nvarchar(MAX),
		@sql nvarchar(max);

DECLARE sql_cursor CURSOR STATIC
FOR	SELECT	[db],
			[sql]
	FROM @tblSql
	ORDER BY id;

OPEN sql_cursor;

WHILE 1=1
BEGIN
	FETCH NEXT FROM sql_cursor INTO @db, @sql; IF NOT @@FETCH_STATUS = 0 BREAK

	SET @sql = N'USE ' + QUOTENAME( @db ) + N';' + @crlf + @crlf + @sql

	IF @txtOnly = 1
		PRINT @sql + @crlf + @crlf
	ELSE
	BEGIN
		BEGIN TRY
			EXEC( @sql );
		END TRY
		BEGIN CATCH

			EXEC dbo.usp_error_handler

			CLOSE sql_cursor
			DEALLOCATE sql_cursor

			WHILE @@TRANCOUNT > @tc
				 ROLLBACK TRANSACTION;

			RETURN -1;
		END CATCH
	END
END

CLOSE sql_cursor
DEALLOCATE sql_cursor

RETURN 0;
GO


CREATE PROCEDURE [dbo].[usp_collationconvert] @db nvarchar(max), @newcollation nvarchar(max), @txtOnly bit = 0
AS
SET NOCOUNT ON;

DECLARE @tblSql tblSql,
		@id bigint,
		@sql nvarchar(max),
		@rv int;

	-- Enable read-write mode
	IF NOT ( SELECT is_read_only FROM sys.databases WHERE name = @db ) = 0
		INSERT INTO @tblSql( [db], [sql] )
		SELECT 'master', 'ALTER DATABASE ' + QUOTENAME( @db) + ' SET READ_WRITE WITH NO_WAIT;'

EXEC @rv = dbo.usp_executeSql @tblSql = @tblSql, @txtOnly = @txtOnly;
IF NOT @rv = 0 RETURN -1;
DELETE FROM @tblSql;
	
	-- Kill connections to the database
	INSERT INTO @tblSql( [db], [sql] )
	SELECT 'master', 'KILL ' + CONVERT( varchar(max), p.spid )
	FROM sys.sysprocesses	p
	JOIN sys.databases		d ON d.database_id = p.[dbid]
	WHERE	loginame <> 'sa'
	AND		d.name = @db
	AND		p.spid <> @@SPID
	
	-- SET Compatibility level to 2008 R2
	IF NOT ( SELECT compatibility_level FROM sys.databases WHERE name = @db ) = 100
		INSERT INTO @tblSql( [db], [sql] )
		SELECT 'master', 'ALTER DATABASE ' + QUOTENAME( @db) + ' SET COMPATIBILITY_LEVEL = 100;'
	
EXEC @rv = dbo.usp_executeSql @tblSql = @tblSql, @txtOnly = @txtOnly;
IF NOT @rv = 0 RETURN -1;
DELETE FROM @tblSql;

	-- Drop computed columns
	SET @sql = N'
	SELECT '''+ @db + ''', ''ALTER TABLE '' + QUOTENAME( s.name ) + ''.'' + QUOTENAME( t.name ) + '' DROP COLUMN '' + QUOTENAME( c.name ) + '';'' AS statements
	FROM ' + QUOTENAME( @db ) + '.sys.computed_columns	c 
	JOIN ' + QUOTENAME( @db ) + '.sys.tables				t ON t.object_id = c.object_id
	JOIN ' + QUOTENAME( @db ) + '.sys.schemas			s ON s.schema_id = t.schema_id
	WHERE c.collation_name <> ''' + @newcollation + ''''

	INSERT INTO @tblSql( [db], [sql] )
	EXEC( @sql )



EXEC @rv = dbo.usp_executeSql @tblSql = @tblSql, @txtOnly = @txtOnly;
IF NOT @rv = 0 RETURN -1;
DELETE FROM @tblSql;

	-- Drop foreign keys
	SET @sql = N'
	SELECT DISTINCT '''+ @db + ''', N''ALTER TABLE '' + QUOTENAME(S.name) + ''.'' + QUOTENAME(O.NAME) + '' DROP CONSTRAINT '' + QUOTENAME( fk.name ) + '';''
	FROM			' + QUOTENAME( @db ) + '.sys.schemas				s
	INNER JOIN		' + QUOTENAME( @db ) + '.sys.objects				o	ON	o.schema_id = s.schema_id
	INNER JOIN		' + QUOTENAME( @db ) + '.sys.foreign_keys			fk	ON	fk.parent_object_id = o.object_id
	INNER JOIN		' + QUOTENAME( @db ) + '.sys.foreign_key_columns	fkc	ON	fkc.constraint_object_id = fk.object_id
	LEFT OUTER JOIN	' + QUOTENAME( @db ) + '.sys.columns				c	ON	c.object_id = fkc.parent_object_id
																			AND	c.column_id = fkc.parent_column_id
	WHERE	c.collation_name <> ''' + @newcollation + ''' '

	INSERT INTO @tblSql( [db], [sql] )
	EXEC( @sql )
	

	-- Drop Check constraints
	SET @sql = N'
	SELECT DISTINCT '''+ @db + ''', N''ALTER TABLE '' + QUOTENAME(S.name) + ''.'' + QUOTENAME(O.NAME) + '' DROP CONSTRAINT '' + QUOTENAME( cc.name ) + '';''
	FROM			' + QUOTENAME( @db ) + '.sys.schemas			s
	INNER JOIN		' + QUOTENAME( @db ) + '.sys.objects			o	ON	o.schema_id = s.schema_id
	INNER JOIN		' + QUOTENAME( @db ) + '.sys.check_constraints	cc	ON	cc.parent_object_id = o.object_id
	LEFT OUTER JOIN	' + QUOTENAME( @db ) + '.sys.columns			c	ON	c.object_id = cc.parent_object_id
																		AND	c.column_id = cc.parent_column_id
	WHERE	c.collation_name <> ''' + @newcollation + '''
	OR		(
				cc.uses_database_collation = 1
			AND (
				  SELECT    collation_name
				  FROM      sys.databases
				  WHERE     name = ''' + @db + '''
				) <> ''' + @newcollation + '''
			)'

	INSERT INTO @tblSql( [db], [sql] )
	EXEC( @sql )


EXEC @rv = dbo.usp_executeSql @tblSql = @tblSql, @txtOnly = @txtOnly;
IF NOT @rv = 0 RETURN -1;
DELETE FROM @tblSql;

	-- Drop nonclustered indexes
	SET @sql = N'
	SELECT DISTINCT N'''+ @db + ''', N''DROP INDEX '' + QUOTENAME(  i.name ) + '' ON '' + QUOTENAME( s.name ) + ''.'' + QUOTENAME( o.name ) + '';'' AS statements
	FROM ' + QUOTENAME( @db ) + '.sys.indexes		i
	JOIN ' + QUOTENAME( @db ) + '.sys.objects		o	ON	o.object_id = i.object_id
	JOIN ' + QUOTENAME( @db ) + '.sys.schemas		s	ON	s.schema_id = o.schema_id
	JOIN ' + QUOTENAME( @db ) + '.sys.index_columns	ic	ON	ic.object_id = i.object_id
														AND ic.index_id = i.index_id
	JOIN ' + QUOTENAME( @db ) + '.sys.columns		c	ON	c.object_id = ic.object_id
														AND c.column_id = ic.column_id
	WHERE	i.[type] = 2
	AND		o.type NOT IN ( ''S'' )
	AND		i.is_unique_constraint = 0
	AND		i.is_primary_key = 0
	AND		c.collation_name <> ''' + @newcollation + ''''

	INSERT INTO @tblSql( [db], [sql] )
	EXEC( @sql )


	-- Drop nonclustered Unique Constraints and Primary Keys
	SET @sql = N'
	SELECT DISTINCT N'''+ @db + ''', N''ALTER TABLE '' + QUOTENAME( s.name ) + ''.'' + QUOTENAME( o.name ) + '' DROP CONSTRAINT '' + QUOTENAME( uq.name ) + '';''
	FROM ' + QUOTENAME( @db ) + '.sys.schemas		s
	JOIN ' + QUOTENAME( @db ) + '.sys.objects		o	ON	o.schema_id = s.schema_id
	JOIN ' + QUOTENAME( @db ) + '.sys.objects		uq	ON	uq.parent_object_id = o.object_id
	JOIN ' + QUOTENAME( @db ) + '.sys.indexes		i	ON	i.object_id = uq.parent_object_id
														AND i.name = uq.name
	JOIN ' + QUOTENAME( @db ) + '.sys.index_columns	ic	ON	ic.object_id = i.object_id
														AND	ic.index_id = i.index_id
	JOIN ' + QUOTENAME( @db ) + '.sys.columns		c	ON	c.object_id = ic.object_id
														AND	c.column_id = IC.column_id
	WHERE	c.collation_name <> ''' + @newcollation + '''
	AND		i.type = 2
	AND		o.type NOT IN ( ''S'' )'

	INSERT INTO @tblSql( [db], [sql] )
	EXEC( @sql )


	-- Drop clustered indexes (exclude constraints)
	SET @sql = N'
	SELECT DISTINCT N'''+ @db + ''', N''DROP INDEX '' + QUOTENAME(  i.name ) + '' ON '' + QUOTENAME( s.name ) + ''.'' + QUOTENAME( o.name ) + '';'' AS statements
	FROM ' + QUOTENAME( @db ) + '.sys.indexes		i
	JOIN ' + QUOTENAME( @db ) + '.sys.objects		o	ON	o.object_id = i.object_id
	JOIN ' + QUOTENAME( @db ) + '.sys.schemas		s	ON	s.schema_id = o.schema_id
	JOIN ' + QUOTENAME( @db ) + '.sys.index_columns	ic	ON	ic.object_id = i.object_id
														AND ic.index_id = i.index_id
	JOIN ' + QUOTENAME( @db ) + '.sys.columns		c	ON	c.object_id = ic.object_id
														AND c.column_id = ic.column_id
	WHERE	i.[type] = 1
	AND		i.is_unique_constraint = 0
	AND		i.is_primary_key = 0
	AND		o.type NOT IN ( ''S'' )
	AND		c.collation_name <> ''' + @newcollation + ''''

	INSERT INTO @tblSql( [db], [sql] )
	EXEC( @sql )


	-- Drop clustered Unique Constraints and Primary Keys
	SET @sql = N'
	SELECT DISTINCT N'''+ @db + ''', N''ALTER TABLE '' + QUOTENAME( s.name ) + ''.'' + QUOTENAME( o.name ) + '' DROP CONSTRAINT '' + QUOTENAME( uq.name ) + '';''
	FROM ' + QUOTENAME( @db ) + '.sys.schemas		s
	JOIN ' + QUOTENAME( @db ) + '.sys.objects		o	ON	o.schema_id = s.schema_id
	JOIN ' + QUOTENAME( @db ) + '.sys.objects		uq	ON	uq.parent_object_id = o.object_id
	JOIN ' + QUOTENAME( @db ) + '.sys.indexes		i	ON	i.object_id = uq.parent_object_id
														AND i.name = uq.name
	JOIN ' + QUOTENAME( @db ) + '.sys.index_columns	ic	ON	ic.object_id = i.object_id
														AND	ic.index_id = i.index_id
	JOIN ' + QUOTENAME( @db ) + '.sys.columns		c	ON	c.object_id = ic.object_id
														AND	c.column_id = IC.column_id
	WHERE	c.collation_name <> ''' + @newcollation + '''
	AND		o.type NOT IN ( ''S'' )
	AND		i.type = 1'

	INSERT INTO @tblSql( [db], [sql] )
	EXEC( @sql )


EXEC @rv = dbo.usp_executeSql @tblSql = @tblSql, @txtOnly = @txtOnly;
IF NOT @rv = 0 RETURN -1;
DELETE FROM @tblSql;

	-- Drop statistics
	SET @sql = N'
	SELECT DISTINCT N'''+ @db + ''', N''DROP STATISTICS '' + QUOTENAME( s.name ) + ''.'' + QUOTENAME( o.name ) + ''.'' + QUOTENAME( st.name ) + '';''
	FROM ' + QUOTENAME( @db ) + '.sys.stats			st
	JOIN	(
				--exclude index stats
				SELECT object_id, stats_id
				FROM ' + QUOTENAME( @db ) + '.sys.stats
				EXCEPT
				SELECT object_id, index_id
				FROM ' + QUOTENAME( @db ) + '.sys.indexes
			)				stat	ON	stat.object_id = st.object_id
									AND stat.stats_id = st.stats_id

	JOIN ' + QUOTENAME( @db ) + '.sys.objects		o	ON	o.object_id = st.object_id
	JOIN ' + QUOTENAME( @db ) + '.sys.schemas		s	ON	s.schema_id = o.schema_id
	JOIN ' + QUOTENAME( @db ) + '.sys.stats_columns	sc	ON	sc.object_id = stat.object_id
														AND sc.stats_id = stat.stats_id
	JOIN ' + QUOTENAME( @db ) + '.sys.columns		c	ON	c.object_id = sc.object_id
														AND c.column_id = sc.column_id
	WHERE	o.[type] IN ( ''U'', ''V'', ''TF'' )
	AND		s.name <> ''sys''
	ORDER BY 1'

	INSERT INTO @tblSql( [db], [sql] )
	EXEC( @sql )


EXEC @rv = dbo.usp_executeSql @tblSql = @tblSql, @txtOnly = @txtOnly;
IF NOT @rv = 0 RETURN -1;
DELETE FROM @tblSql;

	-- Kill connections to the database
	INSERT INTO @tblSql( [db], [sql] )
	SELECT N'master', N'KILL ' + CONVERT( varchar(max), p.spid )
	FROM sys.sysprocesses	p
	JOIN sys.databases		d ON d.database_id = p.[dbid]
	WHERE	loginame <> 'sa'
	AND		d.name = @db
	AND		p.spid <> @@SPID

	-- Change databasecollation
	IF NOT ( SELECT collation_name FROM sys.databases WHERE name = @db ) = @newcollation
		INSERT INTO @tblSql( [db], [sql] )
		SELECT N'master', N'ALTER DATABASE ' + QUOTENAME( @db) + ' COLLATE ' + @newcollation + ';'

EXEC @rv = dbo.usp_executeSql @tblSql = @tblSql, @txtOnly = @txtOnly;
IF NOT @rv = 0 RETURN -1;
DELETE FROM @tblSql;


	-- ALter collation of system types
	SET @sql = N'
	SELECT N'''+ @db + ''', N''ALTER TABLE '' + QUOTENAME( s.name ) + ''.'' + QUOTENAME( t.name ) + '' ALTER COLUMN '' + QUOTENAME( c.name ) + '' '' + QUOTENAME( tp.name ) + '' ''
			+ CASE	WHEN tp.name IN ( ''text'', ''ntext'', ''sysname'' )
						THEN ''''
					WHEN c.max_length = -1
						THEN ''( max ) ''
					WHEN tp.name IN ( ''char'', ''varchar'' )
						THEN ''( '' + CONVERT( nvarchar(max), c.max_length ) + '' ) ''
					WHEN tp.name IN ( ''nchar'', ''nvarchar'' )
						THEN ''( '' + CONVERT( nvarchar(max), c.max_length/2 ) + '' ) ''
					ELSE ''( max ) '' END
			+ ''COLLATE ' + @newcollation + ' ''
			+ CASE WHEN c.is_nullable = 1 THEN ''NULL'' ELSE ''NOT NULL'' END + '';''
	FROM ' + QUOTENAME( @db ) + '.sys.columns	c
	JOIN ' + QUOTENAME( @db ) + '.sys.types		tp	ON	tp.user_type_id = c.user_type_id
	JOIN ' + QUOTENAME( @db ) + '.sys.tables		t ON t.object_id = c.object_id
	JOIN ' + QUOTENAME( @db ) + '.sys.schemas	s ON s.schema_id = t.schema_id
	WHERE c.collation_name <> ''' + @newcollation + '''
	AND t.[type] NOT IN ( ''IT'', ''S'' )
	AND	tp.user_type_id = tp.system_type_id
	ORDER BY 1'

	INSERT INTO @tblSql( [db], [sql] )
	EXEC( @sql )


	-- ALTER collation of user defined types. They can only be changed by converting to system type and back

	-- Change user types to system types...
	SET @sql = N'
	SELECT N'''+ @db + ''', N''ALTER TABLE '' + QUOTENAME( s.name ) + ''.'' + QUOTENAME( t.name ) + '' ALTER COLUMN '' + QUOTENAME( c.name ) + '' '' + QUOTENAME( tps.name ) + '' ''
			+ CASE	WHEN tps.name IN ( ''text'', ''ntext'', ''sysname'' )
						THEN ''''
					WHEN c.max_length = -1
						THEN ''( max ) ''
					WHEN tps.name IN ( ''char'', ''varchar'' )
						THEN ''( '' + CONVERT( nvarchar(max), c.max_length ) + '' ) ''
					WHEN tps.name IN ( ''nchar'', ''nvarchar'' )
						THEN ''( '' + CONVERT( nvarchar(max), c.max_length/2 ) + '' ) ''
					ELSE ''( max ) '' END
			+ ''COLLATE ' + @newcollation + ' ''
			+ CASE WHEN c.is_nullable = 1 THEN ''NULL'' ELSE ''NOT NULL'' END + '';''
	FROM ' + QUOTENAME( @db ) + '.sys.columns	c
	JOIN ' + QUOTENAME( @db ) + '.sys.types		tpu ON	tpu.user_type_id = c.user_type_id
													AND tpu.user_type_id <> tpu.system_type_id
	JOIN ' + QUOTENAME( @db ) + '.sys.types		tps ON tps.user_type_id = tpu.system_type_id
	JOIN ' + QUOTENAME( @db ) + '.sys.tables	t	ON t.object_id = c.object_id
	JOIN ' + QUOTENAME( @db ) + '.sys.schemas	S	ON s.schema_id = t.schema_id
	WHERE c.collation_name <> ''' + @newcollation + '''
	AND t.[type] NOT IN ( ''IT'', ''S'' )
	AND	tpu.user_type_id <> tpu.system_type_id
	ORDER BY 1'

	INSERT INTO @tblSql( [db], [sql] )
	EXEC( @sql )


	-- ... and change back to user type to give it the new collation
	SET @sql = N'
	SELECT N'''+ @db + ''', N''ALTER TABLE '' + QUOTENAME( s.name ) + ''.'' + QUOTENAME( t.name ) + '' ALTER COLUMN '' + QUOTENAME( c.name ) + '' '' + QUOTENAME( tpu.name ) + '' ''
			+ CASE WHEN c.is_nullable = 1 THEN ''NULL'' ELSE ''NOT NULL'' END + '';''
	FROM ' + QUOTENAME( @db ) + '.sys.columns	c
	JOIN ' + QUOTENAME( @db ) + '.sys.types		tpu ON tpu.user_type_id = c.user_type_id
	JOIN ' + QUOTENAME( @db ) + '.sys.tables	t ON t.object_id = c.object_id
	JOIN ' + QUOTENAME( @db ) + '.sys.schemas	s ON s.schema_id = t.schema_id
	WHERE c.collation_name <> ''' + @newcollation + '''
	AND t.[type] NOT IN ( ''IT'', ''S'' )
	AND	tpu.user_type_id <> tpu.system_type_id
	ORDER BY 1'

	INSERT INTO @tblSql( [db], [sql] )
	EXEC( @sql )


EXEC @rv = dbo.usp_executeSql @tblSql = @tblSql, @txtOnly = @txtOnly;
IF NOT @rv = 0 RETURN -1;
DELETE FROM @tblSql;

RETURN 0;
GO
